/**
 * ______________________________________________________________________________
 *
 * File: AccessDatabase.java
 *______________________________________________________________________________
 *
 * CreatedBy: Verinon
 * CreationDate: Sep 12, 2012   1:41:08 PM   2012
 * Description: Write your class description here like from where it is calling and
 * what functions are doing
 *______________________________________________________________________________
 *
 * Copyright: (c) Vietinbank, all rights reserved 
 *______________________________________________________________________________
 *
 */
package com.vb.ecm.migration.icdoc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.ResourceBundle;

import javax.swing.JOptionPane;

import org.apache.log4j.Logger;

/**
 * @Last Midify Author Verinon
 * @Last Midification Date Sep 12, 2012
 * @Last Midification Time 1:41:08 PM
 * @Last Midification Year 2012
 * @What to do next TODO
 */
public class AccessDatabase {

	private static ResourceBundle RSB = ResourceBundle.getBundle("com.vb.ecm.migration.icdoc.migrationtool");
	private static String ACCESSDBPATH = RSB.getString("ACCESS_DATABASE_PATH");
	private static final Logger LOGGER = Logger.getLogger(AccessDatabase.class);
	/*
	 * 
	 */
	public AccessDatabase() {
		// TODO Auto-generated constructor stub
	}

	public int getIngestedFoldersCount(String dbname, Statement accessStatement) throws SQLException {
		LOGGER.debug("AccessDatabase  :: getIngestedFoldersCount :: Enter into Method");
		ResultSet resultset = null;
		int NoOfRecordsCount = 0;
		try {
			String noOfRecordsQuery = "select count(*) as cnt from icdocfolders";
			resultset = accessStatement.executeQuery(noOfRecordsQuery);
			while (resultset.next()) {
				NoOfRecordsCount = resultset.getInt("cnt");
				LOGGER.debug("AccessDatabase  :: getIngestedFoldersCount :: NoOfRecordsCount"+NoOfRecordsCount);
			}
		} catch (Exception e) {
			//JOptionPane.showMessageDialog(null, "MT006-EError while accessing Access Database");
			LOGGER.error("AccessDatabase  :: getIngestedFoldersCount :: AD001"+e);
			e.printStackTrace();
		}
		LOGGER.debug("AccessDatabase  :: getIngestedFoldersCount :: Exit from Method");
		return NoOfRecordsCount;
	}
	
	
	public boolean insertExportedFolderIntoAccessDB(String Code, String numberSign, String exportDate, String Status, Statement accessStatement ) throws SQLException {
		LOGGER.debug("AccessDatabase  :: insertExportedFolderIntoAccessDB :: Enter into Method");
		boolean updated = false;
		try {
			String insertFolderQ = "INSERT INTO icdocfolders(Folder_Code, Number_Sign, Export_Date, Export_Status ) VALUES('"+Code+"','"+numberSign+"','"+exportDate+"','"+Status+"')";
			accessStatement.executeUpdate(insertFolderQ);
		} catch (Exception e) {
			//JOptionPane.showMessageDialog(null, "MT007-EError while accessing Access Database");
			LOGGER.error("AccessDatabase  :: insertExportedFolderIntoAccessDB :: AD002"+e);
			e.printStackTrace();
		}
		LOGGER.debug("AccessDatabase  :: insertExportedFolderIntoAccessDB :: Exit from Method");
		return updated;
	}
	
	public boolean updateExportedFolderIntoAccessDB(String Code, String numberSign, String exportDate, String Status, Statement accessStatement ) throws SQLException {
		LOGGER.debug("AccessDatabase  :: insertExportedFolderIntoAccessDB :: Enter into Method");
		boolean updated = false;
		try {
			String insertFolderQ = "UPDATE icdocfolders SET Export_Date = '"+exportDate+"', Export_Status = '"+Status+"' where Number_Sign= '"+numberSign+"';";
			accessStatement.executeUpdate(insertFolderQ);
		} catch (Exception e) {
			//JOptionPane.showMessageDialog(null, "MT007-EError while accessing Access Database");
			LOGGER.error("AccessDatabase  :: insertExportedFolderIntoAccessDB :: AD002"+e);
			e.printStackTrace();
		}
		LOGGER.debug("AccessDatabase  :: insertExportedFolderIntoAccessDB :: Exit from Method");
		return updated;
	}
	
	
	public boolean insertExportedDocsIntoAccessDB(String Code, String numberSign, String objectId, String exportDate, String exportStatus, Statement accessStatement ) throws SQLException {
		LOGGER.debug("AccessDatabase  :: insertExportedDocsIntoAccessDB :: Enter into Method");
		boolean updated = false;
		try {
			String insertDocsQ = "INSERT INTO icdocdocuments(Document_Code, Number_Sign, Object_Id, Export_Date, Export_Status) VALUES('"+Code+"','"+numberSign+"','"+objectId+"','"+exportDate+"','"+exportStatus+"')";
			LOGGER.debug("AccessDatabase  :: insertExportedDocsIntoAccessDB :: insertDocsQ:"+insertDocsQ);
			accessStatement.executeUpdate(insertDocsQ);
		} catch (Exception e) {
			//JOptionPane.showMessageDialog(null, "MT007-EError while accessing Access Database");
			LOGGER.error("AccessDatabase  :: insertExportedDocsIntoAccessDB :: AD003"+e);
			e.printStackTrace();
		}
		LOGGER.debug("AccessDatabase  :: insertExportedDocsIntoAccessDB :: Exit from Method");
		return updated;
	}
	
	
	
	public boolean updateExportedDocsIntoAccessDB(String Code, String numberSign, String objectId, String exportDate, String exportStatus, Statement accessStatement ) throws SQLException {
		LOGGER.debug("AccessDatabase  :: updateExportedDocsIntoAccessDB :: Enter into Method");
		boolean updated = false;
		try {
			String updateDocsQ = "UPDATE icdocdocuments SET Object_Id = '"+objectId+"', Export_Date = '"+exportDate+"', Export_Status = '"+exportStatus+"' where Document_Code= '"+Code+"' and Number_Sign= '"+numberSign+"';";
			LOGGER.debug("AccessDatabase  :: updateExportedDocsIntoAccessDB :: insertDocsQ:"+updateDocsQ);
			accessStatement.executeUpdate(updateDocsQ);
		} catch (Exception e) {
			//JOptionPane.showMessageDialog(null, "MT007-EError while accessing Access Database");
			LOGGER.error("AccessDatabase  :: updateExportedDocsIntoAccessDB :: AD004"+e);
			e.printStackTrace();
		}
		LOGGER.debug("AccessDatabase  :: updateExportedDocsIntoAccessDB :: Exit from  Method");
		return updated;
	}
	
	
	public Object[] getAccessDBFolder() throws SQLException {
		LOGGER.debug("AccessDatabase  :: getAccessDBFolder :: Enter into Method");
		Connection conn1 = getAccessConnectionDetails();
		Statement stmt1 = conn1.createStatement();
		String folderCount = "select count(*) as totalfolder from icdocfolders";
		ResultSet dataCountResultSet = stmt1.executeQuery(folderCount);
		dataCountResultSet.next();
		int totalFolders = dataCountResultSet.getInt("totalfolder");
		
		String succFolCount = "select count(*) as totalsuccfolder from icdocfolders where Export_Status='SUCCESS'";
		ResultSet succFolCountResultSet = stmt1.executeQuery(succFolCount);
		succFolCountResultSet.next();
		int totalSuccFolders = succFolCountResultSet.getInt("totalsuccfolder");
		
		int totalFailFolders = totalFolders - totalSuccFolders;
		
		Object[] returnObjects = new Object[3];
		
		returnObjects[0] = totalFolders;
		returnObjects[1] = totalSuccFolders;
		returnObjects[2] = totalFailFolders;
		LOGGER.debug("AccessDatabase  :: getAccessDBFolder :: Exit from Method");
		return returnObjects;
	}
	
	
	public Object[] getAccessDBData() throws SQLException {
		LOGGER.debug("AccessDatabase  :: getAccessDBData :: Enter into Method");
		int successObjects=0, failedObjects=0;
		Connection conn1 = getAccessConnectionDetails();
		Statement stmt1 = conn1.createStatement();
		String dataCount = "select count(*) as totaldata from icdocdocuments";
		ResultSet dataCountResultSet = stmt1.executeQuery(dataCount);
		dataCountResultSet.next();
		int totalData = dataCountResultSet.getInt("totaldata");
		Object[] returnObjects = new Object[4];
		
		String[][] columnsData= new String[totalData][5];
		try {
			Connection conn2 = getAccessConnectionDetails();
			Statement stmt2 = conn2.createStatement();			
			String selectData = "select * from icdocdocuments order by Document_Code";								
			ResultSet dataResultSet = stmt2.executeQuery(selectData);			
			int i = 0;
			while (dataResultSet.next()) {
								
				columnsData[i][0] = dataResultSet.getString("Document_Code");				
				columnsData[i][1] = dataResultSet.getString("Number_Sign");				
				columnsData[i][2] = dataResultSet.getString("Object_Id");
				columnsData[i][3] = dataResultSet.getString("Export_Date");
				columnsData[i][4] = dataResultSet.getString("Export_Status");				
				if(( columnsData[i][4]).equalsIgnoreCase("SUCCESS")) {
					successObjects = successObjects + 1;
				} else {
					failedObjects = failedObjects + 1;
				}
				i = i + 1;
			}		
			
		} catch (Exception e) {
			//JOptionPane.showMessageDialog(null, "MT007-EError while accessing Access Database");
			LOGGER.error("AccessDatabase  :: getAccessDBData :: AD005"+e);
			e.printStackTrace();
		}
		returnObjects[0] = columnsData;
		returnObjects[1] = successObjects;
		returnObjects[2] = failedObjects;
		returnObjects[3] = totalData;
		LOGGER.debug("AccessDatabase  :: getAccessDBData :: Exit from Method");
		return returnObjects;
	}

	/**
	 * Method Name : getConnectionDetails Description : This method will get and
	 * set the database connection thru DSN Creation Date : 07 March 2011
	 * 
	 * @author Mahesh.B
	 **/
	public Connection getAccessConnectionDetails() {
		
		LOGGER.debug("AccessDatabase :: getAccessConnectionDetails ::Enter into Method");
		Connection Con = null;
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			LOGGER.debug("AccessDatabase :: getAccessConnectionDetails :: " +StartMigration.CURRENTDIRECTORY+ACCESSDBPATH);
			String myDB = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" +StartMigration.CURRENTDIRECTORY+ACCESSDBPATH + "";
			Con = DriverManager.getConnection(myDB, " ", " ");
			// LOGGER.debug("Migration Access DataBase connected");
		} catch (Exception e) {
			JOptionPane.showMessageDialog(null, "MT007-Error While connecting to Access Database");
			LOGGER.error("AccessDatabase  :: getAccessConnectionDetails :: AD006"+e);	
			e.printStackTrace();
		}
		LOGGER.debug("AccessDatabase :: getAccessConnectionDetails :: Exit from Method");
		return Con;
	}

	/**
	 * Method Name : getCollectionClose Description : This method will close the
	 * collections and connections. Creation Date : 07 March 2011
	 * 
	 * @author Mahesh.B
	 * @exception Exception
	 **/
	private void getCollectionClose(Statement sqlStatement, Connection Con) {
		try {
			sqlStatement.close();
			Con.close();
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null, "MT008-Error while closing collection");
			LOGGER.error("AccessDatabase  :: getCollectionClose :: AD007"+e);
			e.printStackTrace();
		}
	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {
	
		
		// TODO: This class to export all documents from ECM to EccessDB
		// TODO : Test functions here !

	}

}
